package com.pls.data;

import java.util.ArrayList;
import java.util.List;

import com.pls.entity.EmployeeSeat;
import com.pls.entity.Location;
import com.pls.entity.LocationDetails;
import com.pls.mapper.EmployeeSeatMapper;

public class EmployeeSeatData {
	
//			private StringBuilder selectEmployeeSeat = new StringBuilder(
//			"select seat.seat_id, seat.seat_code, seat.quadrant, seat.local_no, seat.col, seat.row, emp.employee_id, emp.first_name, emp.last_name, emp.email_add, emp.position_code, st.seat_type_id, st.seat_type_code, st.seat_type_desc, LD.location_detail_id, LD.floor_level, loc.location_id, loc.location_address, loc.location_code from seat join employee as emp on emp.employee_id = seat.employee_id join location_detail as LD on LD.location_detail_id = seat.location_detail_id join location as loc on loc.location_id = LD.location_id join seat_type as st on st.seat_type_id = seat.seat_type_id ");

			private StringBuilder selectEmployeeSeat = new StringBuilder(
			"select seat.employee_id, seat.seat_id, seat.seat_code, seat.quadrant, seat.local_no, seat.col, seat.row,st.seat_type_id, st.seat_type_code, st.seat_type_desc, LD.location_detail_id, LD.floor_level, loc.location_id, loc.location_address, loc.location_code from seat join location_detail as LD on LD.location_detail_id = seat.location_detail_id join location as loc on loc.location_id = LD.location_id join seat_type as st on st.seat_type_id = seat.seat_type_id ");

	public EmployeeSeat getEmployeeSeatDetails(int employeeId) {
		EmployeeSeat result = null;
		selectEmployeeSeat = new StringBuilder(
				"select seat.seat_id, seat.seat_code, seat.quadrant, seat.local_no, seat.col, seat.row, emp.employee_id, emp.first_name, emp.last_name, emp.email_add, emp.position_code, st.seat_type_id, st.seat_type_code, st.seat_type_desc, LD.location_detail_id, LD.floor_level, loc.location_id, loc.location_address, loc.location_code from seat join employee as emp on emp.employee_id = seat.employee_id join location_detail as LD on LD.location_detail_id = seat.location_detail_id join location as loc on loc.location_id = LD.location_id join seat_type as st on st.seat_type_id = seat.seat_type_id ");
		selectEmployeeSeat.append(" WHERE emp.employee_id = " + employeeId);
		EmployeeSeatMapper mapper = new EmployeeSeatMapper();
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

		result = dataHelper.ExecuteQuerySingle(selectEmployeeSeat.toString(),
				mapper);

		return result;
	}

	// Returns List of Employee seat by Floor and By Quadrant
	public List<EmployeeSeat> getEmployeeSeatsByFloorAndQuadrant(
			String quadrant, String floor) {
		List<EmployeeSeat> result = null;
		selectEmployeeSeat = new StringBuilder(
				"select seat.employee_id, seat.seat_id, seat.seat_code, seat.quadrant, seat.local_no, seat.col, seat.row,st.seat_type_id, st.seat_type_code, st.seat_type_desc, LD.location_detail_id, LD.floor_level, loc.location_id, loc.location_address, loc.location_code from seat join location_detail as LD on LD.location_detail_id = seat.location_detail_id join location as loc on loc.location_id = LD.location_id join seat_type as st on st.seat_type_id = seat.seat_type_id ");
		selectEmployeeSeat.append(" WHERE seat.quadrant = '" + quadrant + "'");
		selectEmployeeSeat.append(" AND LD.floor_level = '" + floor + "'");
		selectEmployeeSeat.append(" ORDER BY seat.row, seat.col asc");
		EmployeeSeatMapper mapper = new EmployeeSeatMapper();
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

		result = dataHelper.ExecuteQuery(selectEmployeeSeat.toString(), mapper);

		return result;
	}

	public boolean validateSeatCodeExist(String newSeatCode) {

		boolean isSeatCodeExist = false;

		// String validateSeatCodeExist =
		// "Select * from seat where seat_code = \""+newSeatCode+"\"";

		selectEmployeeSeat.append(" WHERE seat.seat_code = '" + newSeatCode
				+ "'");

		List<EmployeeSeat> employeeSeatList = new ArrayList<EmployeeSeat>();
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();
		EmployeeSeatMapper mapper = new EmployeeSeatMapper();

		employeeSeatList = dataHelper.ExecuteQuery(
				selectEmployeeSeat.toString(), mapper);

		if (employeeSeatList.size() > 0) {
			isSeatCodeExist = true;
		}

		return isSeatCodeExist;
	}

	/*
	 * public boolean validateEmployeeSeatOccupied(String newSeatCode, boolean
	 * isSeatCodeExist) {
	 * 
	 * boolean isEmployeeSeatOccupied = false;
	 * 
	 * if (isSeatCodeExist == true) {
	 * 
	 * String validateEmployeeSeatOccupied =
	 * "Select * from seat where seat_code = "+ newSeatCode;
	 * 
	 * List<EmployeeSeat> employeeSeatList = new ArrayList<EmployeeSeat>();
	 * DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();
	 * EmployeeSeatMapper mapper = new EmployeeSeatMapper();
	 * 
	 * employeeSeatList = dataHelper.ExecuteQuery(validateEmployeeSeatOccupied,
	 * mapper);
	 * 
	 * if (employeeSeatList.isEmpty() != false){ isEmployeeSeatOccupied = true;
	 * } }
	 * 
	 * return isEmployeeSeatOccupied;
	 * 
	 * }
	 */
	public List<EmployeeSeat> selectEmployeeSeatInformation(String newSeatCode,
			boolean isSeatCodeExist) {

		List<EmployeeSeat> result = null;

		if (isSeatCodeExist == true) {

			// String selectEmployeeSeatInformation =
			// "Select * from seat where seat_code = \""+newSeatCode+"\"";
			selectEmployeeSeat.append(" WHERE seat.seat_code = '" + newSeatCode
					+ "'");
			List<EmployeeSeat> employeeSeatList = new ArrayList<EmployeeSeat>();
			EmployeeSeatMapper mapper = new EmployeeSeatMapper();
			DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

			result = dataHelper.ExecuteQuery(selectEmployeeSeat.toString(),
					mapper);

			System.out.println("result: " + result);
		}

		return result;

	}

	public int insertEmployeeSeat(String employeeId, EmployeeSeat empSeat,
			boolean isSeatCodeExist, boolean isEmployeeSeatCodeOccupied) {

		int rowCount = 0;

		if (isSeatCodeExist == true && isEmployeeSeatCodeOccupied == true) {

			Location location = new Location();
			location = empSeat.getLocation();

			LocationDetails locDetails = new LocationDetails();
			int location_detail_id = locDetails.getId();

			String insertEmployeeSeat = "INSERT INTO "
					+ "pls.seat(seat_code, quadrant, location_detail_id, "
					+ "employee_id, local_no, seat_type_id, col, row) VALUES ("
					+ empSeat.getSeatCode() + ", " + empSeat.getQuadrant()
					+ ", " + location_detail_id + ", " + employeeId + ", "
					+ empSeat.getLocalNo() + ", "
					+ empSeat.getSeatType().getSeat_type_id() + ", "
					+ empSeat.getRow() + ", " + empSeat.getCol() + ")";

			DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

			// rowCount = dataHelper.ExecuteUpdate(insertEmployeeSeat);

		}

		return rowCount;

	}

	public int updateEmployeeSeat(int employeeId, String seatCode, int seatId) {
		int rowUpdateCount = 0;

		String updateEmployeeSeat = "UPDATE seat SET employee_id = "
				+ employeeId + ",seat_code = \"" + seatCode + "\""
				+ " WHERE seat_id = " + seatId;

		List<EmployeeSeat> employeeSeatList = new ArrayList<EmployeeSeat>();
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

		// rowUpdateCount = dataHelper.ExecuteUpdate(updateEmployeeSeat);

		System.out.println("rowUpdateCount: " + rowUpdateCount);

		return rowUpdateCount;
	}
	

	public int deleteEmployeeSeat(int employeeId, String seatCode) {
		int rowUpdateCount = 0;

		String deleteEmployeeSeat = "UPDATE seat SET employee_id = null WHERE seat_code = "
				+ seatCode;

		/*
		 * String deleteEmployeeSeat = "DELETE FROM seat " +
		 * " WHERE employee_id = " + employeeId +
		 * " and seat_code = \""+seatCode+"\"";
		 */
		List<EmployeeSeat> employeeSeatList = new ArrayList<EmployeeSeat>();
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

		// rowUpdateCount = dataHelper.ExecuteUpdate(deleteEmployeeSeat);

		return rowUpdateCount;
	}
	
	public boolean hardDeleteById(int seatId){
		int rowsAffected = 0;
		boolean result = false;
		selectEmployeeSeat = new StringBuilder("DELETE FROM seat WHERE seat_id = '" + seatId + "'");
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();
		
		rowsAffected = dataHelper.ExecuteUpdate(selectEmployeeSeat.toString());
		
		if(rowsAffected > 0)
		{
			result = true;
		}
		
		return result;
	}
	
	public boolean softDeleteById(int seatId){
		int rowsAffected = 0;
		boolean result = false;
		selectEmployeeSeat = new StringBuilder("UPDATE seat SET employee_id = null WHERE seat_id = " + seatId);
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();
		
		rowsAffected = dataHelper.ExecuteUpdate(selectEmployeeSeat.toString());
		
		if(rowsAffected > 0)
		{
			result = true;
		}
		
		return result;
	}

	
	public List<EmployeeSeat> getEmployeeSeatBySeatCode(String seatCode) {
		List<EmployeeSeat> result = null;
		selectEmployeeSeat = new StringBuilder(
				"select seat.seat_id, seat.seat_code, seat.quadrant, seat.local_no, seat.col, seat.row, emp.employee_id, emp.first_name, emp.last_name, emp.email_add, emp.position_code, st.seat_type_id, st.seat_type_code, st.seat_type_desc, LD.location_detail_id, LD.floor_level, loc.location_id, loc.location_address, loc.location_code from seat join employee as emp on emp.employee_id = seat.employee_id join location_detail as LD on LD.location_detail_id = seat.location_detail_id join location as loc on loc.location_id = LD.location_id join seat_type as st on st.seat_type_id = seat.seat_type_id ");
		selectEmployeeSeat.append(" WHERE seat.seat_code = '" + seatCode + "'");
		EmployeeSeatMapper mapper = new EmployeeSeatMapper();
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();

		result = dataHelper.ExecuteQuery(selectEmployeeSeat.toString(), mapper);

		return result;
	}
	
	public int insertEmployeeSeat(EmployeeSeat employeeSeat){
		int result = 0;
		
		selectEmployeeSeat = new StringBuilder("INSERT INTO seat (seat.employee_id, seat.seat_type_id) VALUES ('" + employeeSeat.getEmployee().getId() + "', 2)");
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();
		System.out.println(selectEmployeeSeat.toString());
		result = dataHelper.ExecuteUpdate(selectEmployeeSeat.toString());
		
		return result;
	}
	
	public int insertEmployeeSeat(int employeeId, String floorLevel, EmployeeSeat empSeat, boolean isSeatCodeExist)
	{
		int result = 0;

		if(!isSeatCodeExist)
		{
			return result;
		}
		
		selectEmployeeSeat = new StringBuilder("INSERT INTO seat (seat.employee_id, seat.seat_type_id) VALUES ('" + empSeat.getEmployee().getId() + "', 2)");
		DataHelper<EmployeeSeat> dataHelper = new DataHelper<EmployeeSeat>();
		System.out.println(selectEmployeeSeat.toString());
		result = dataHelper.ExecuteUpdate(selectEmployeeSeat.toString());
		
		return result;
	}

}
